<?php
/**
 * StatZone default Player controller
 * 
 * @package    USS.StatZone
 * @subpackage Components
 * @link www.ugenix.co.za www.statzone.co.za
 * @license		Commercial
 * @author Salie Hendricks
 */

// no direct access
defined( '_JEXEC' ) or die( 'Restricted access' );

jimport( 'joomla.application.component.controller' );

/**
 * @package		USS
 * @subpackage	StatZone
 */
class StatZoneControllerPlayer extends JController
{
	/**
	 * constructor (registers additional tasks to methods)
	 * @return void
	 */
	function __construct()
	{
		parent::__construct();

        // Register Extra tasks
		$this->registerTask( 'add'  , 	'edit' );
        $this->registerTask( 'apply',		'save' );
        $this->registerTask( 'unpublish',	'publish' );
	}

    /**
	 * Display the list of teams
	 */
	function display()
	{
		global $mainframe;

		$db =& JFactory::getDBO();

		$context			= 'com_statzone.player.list.';
		$filter_order		= $mainframe->getUserStateFromRequest( $context.'filter_order',		'filter_order',		'surname',	'cmd' );
		$filter_order_Dir	= $mainframe->getUserStateFromRequest( $context.'filter_order_Dir',	'filter_order_Dir',	'',			'word' );
		$filter_provid		= $mainframe->getUserStateFromRequest( $context.'filter_provid',		'filter_provid',		'',			'int' );
        $filter_clubid		= $mainframe->getUserStateFromRequest( $context.'filter_clubid',		'filter_clubid',		'',			'int' );
        $filter_teamid		= $mainframe->getUserStateFromRequest( $context.'filter_teamid',		'filter_teamid',		'',			'int' );
		$search				= $mainframe->getUserStateFromRequest( $context.'search',			'search',			'',			'string' );

		$limit		= $mainframe->getUserStateFromRequest( 'global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int' );
		$limitstart = $mainframe->getUserStateFromRequest( $context.'limitstart', 'limitstart', 0, 'int' );

		$where = array();
        
		if ($filter_provid) {
			$where[] = 'c.provinceid = ' . (int) $filter_provid;
		}
        if ($filter_clubid) {
			$where[] = 'p.clubid = ' . (int) $filter_clubid;
		}
        if ($filter_teamid) {
			$where[] = 'pt.teamid = ' . (int) $filter_teamid;
		}
        
		if ($search) {
			$where[] = 'LOWER(p.firstname) LIKE '.$db->Quote( '%'.$db->getEscaped( $search, true ).'%', false )
                       .' OR LOWER(p.surname) LIKE '.$db->Quote( '%'.$db->getEscaped( $search, true ).'%', false );
		}

		$where		= count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '';
		$orderby	= ' ORDER BY '. $filter_order .' '. $filter_order_Dir ;

		// get the total number of records
		$query = 'SELECT COUNT(*)'
		. ' FROM #__statzone_players AS p'
		. ' INNER JOIN #__statzone_clubs AS c ON c.id = p.clubid'
		. ' INNER JOIN #__statzone_province AS province ON province.id = c.provinceid'
        . ' INNER JOIN #__content AS content ON c.contentid = content.id'
        . $where
		;
        
		$db->setQuery( $query );
		$total = $db->loadResult();

		jimport('joomla.html.pagination');
		$pageNav = new JPagination( $total, $limitstart, $limit );

		$query = 'SELECT DISTINCT p.id as id, p.clubid as clubid, p.id as provinceid, p.userid as userid, p.contentid as contentid, p.firstname as firstname, p.surname as surname, p.comment as comment, p.idnumber as idnumber, p.cellnumber as cellnumber, p.emailaddress as emailaddress, content.title as clubname, province.name as provincename, p.showPlayer as showplayer'
		. ' FROM #__statzone_players AS p'
		. ' INNER JOIN #__statzone_clubs AS c ON c.id = p.clubid'
		. ' INNER JOIN #__statzone_province AS province ON province.id = c.provinceid'
        . ' INNER JOIN #__content AS content ON c.contentid = content.id'
        . ' LEFT JOIN  #__statzone_playerteams AS pt ON pt.playerid = p.id'
        . $where
		. $orderby
        ;
       	$db->setQuery( $query, $pageNav->limitstart, $pageNav->limit );
       	$rows = $db->loadObjectList();
        //echo $query;
		$javascript		= 'onchange="submitform();"';
		
        // ________build list of provinces ________
        $sql = 'SELECT id as provinceid, name'
		. ' FROM #__statzone_province'
		;
		$db->setQuery($sql);
		if (!$db->query())
		{
			$this->setRedirect( 'index.php?option=com_statzone' );
			return JError::raiseWarning( 500, $db->getErrorMsg() );
		}

		$provincelist[]		= JHTML::_('select.option',  '0', JText::_( '- Select Province -' ), 'provinceid', 'name' );
		$provincelist			= array_merge( $provincelist, $db->loadObjectList() );
		$lists['filter_provid']		= JHTML::_('select.genericlist',   $provincelist, 'filter_provid', 'class="inputbox" size="1"'.$javascript,'provinceid', 'name', $filter_provid );

        // ________build list of clubs________
        $sql = 'SELECT c.id as clubid, content.title AS name'
		. ' FROM #__statzone_clubs AS c'
		. ' INNER JOIN #__content AS content ON c.contentid = content.id'
		. ' INNER JOIN #__statzone_province AS provinces ON c.provinceid = provinces.id'
        ;
        if($filter_provid)
        {
            $sql = $sql . ' WHERE c.provinceid = '.(int) $filter_provid;
        }
        $sql = $sql . ' ORDER BY name';
        
		$db->setQuery($sql);
		if (!$db->query())
		{
			$this->setRedirect( 'index.php?option=com_statzone' );
			return JError::raiseWarning( 500, $db->getErrorMsg() );
		}
		$clublist[]		= JHTML::_('select.option',  '0', JText::_( '- Select Club -' ), 'clubid', 'name' );
		$clublist			= array_merge( $clublist, $db->loadObjectList() );
		$lists['filter_clubid']		= JHTML::_('select.genericlist',   $clublist, 'filter_clubid', 'class="inputbox" size="1"' . $javascript,'clubid', 'name', $filter_clubid );

        // ________build list of teams________
        $sql = 'SELECT t.id as teamid, t.name AS teamname'
		. ' FROM #__statzone_teams AS t'
		. ' INNER JOIN #__statzone_clubs AS c ON c.id = t.clubid'
		. ' INNER JOIN #__statzone_province AS provinces ON c.provinceid = provinces.id'
        ;
        if($filter_clubid)
        {
            $sql = $sql . ' WHERE t.clubid = '.(int) $filter_clubid;
        }
        $sql = $sql . ' ORDER BY t.name';
		$db->setQuery($sql);
		if (!$db->query())
		{
			$this->setRedirect( 'index.php?option=com_statzone' );
			return JError::raiseWarning( 500, $db->getErrorMsg() );
		}
		$teamlist[]		= JHTML::_('select.option',  '0', JText::_( '- Select Team -' ), 'teamid', 'teamname' );
		$teamlist			= array_merge( $teamlist, $db->loadObjectList() );
		$lists['filter_teamid']		= JHTML::_('select.genericlist',   $teamlist, 'filter_teamid', 'class="inputbox" size="1"'. $javascript,'teamid', 'teamname', $filter_teamid );

        // table ordering
		$lists['order_Dir']	= $filter_order_Dir;
		$lists['order']		= $filter_order;

		// search filter
		$lists['search']= $search;

		require_once(JPATH_COMPONENT.DS.'views'.DS.'players.php');
		StatZoneViewPlayers::players( $rows, $pageNav, $lists );
	}
    
	/**
	 * display the edit form
	 * @return void
	 */
	function edit()
	{
        $db		=& JFactory::getDBO();
		$user	=& JFactory::getUser();

        if ($this->_task == 'edit') 
        {
			$cid	= JRequest::getVar('cid', array(0), 'method', 'array');
			$cid	= array((int) $cid[0]);
		} 
        else 
        {
			$cid	= array( 0 );
		}

        $lists = array();
        
		$option = JRequest::getCmd('option');     
        
        $row =& JTable::getInstance('players', 'Table');
		$row->load( $cid[0] );

        // ________build list of provinces ________
        $sql = 'SELECT id as provinceid, name'
		. ' FROM #__statzone_province'
		;
		$db->setQuery($sql);
		if (!$db->query())
		{
			$this->setRedirect( 'index.php?option=com_statzone' );
			return JError::raiseWarning( 500, $db->getErrorMsg() );
		}

        $ajaxCallback = 'onchange="document.forms[1].task.value=\'updateClublist\';document.forms[1].Submit.click();"';
		$provincelist[]		= JHTML::_('select.option',  '0', JText::_( '- Select Province -' ), 'provinceid', 'name' );
		$provincelist			= array_merge( $provincelist, $db->loadObjectList() );
        if($row->id > 0 )
        {
            $sql = 'SELECT c.provinceid FROM #__statzone_players p INNER JOIN #__statzone_clubs c ON c.id = p.clubid WHERE p.id = ' . $row->id;
            $provinceid= $db->loadresult();
            $row->provinceid = $provinceid;
        }
        $lists['provinceid']		= JHTML::_('select.genericlist',   $provincelist, 'provinceid', $ajaxCallback.' class="inputbox" size="1"','provinceid', 'name', $row->provinceid );

        // ________build list of clubs________
        $sql = 'SELECT c.id as clubid, content.title AS name'
		. ' FROM #__statzone_clubs AS c'
		. ' INNER JOIN #__content AS content ON c.contentid = content.id'
		. ' INNER JOIN #__statzone_province AS provinces ON c.provinceid = provinces.id'
        . ' ORDER BY content.title'
        ;
		$db->setQuery($sql);
		if (!$db->query())
		{
			$this->setRedirect( 'index.php?option=com_statzone' );
			return JError::raiseWarning( 500, $db->getErrorMsg() );
		}

        $ajaxCallback = 'onchange="document.forms[1].task.value=\'updateTeamlist\';document.forms[1].Submit.click();"';
		$clublist[]		= JHTML::_('select.option',  '0', JText::_( '- Select Club -' ), 'clubid', 'name' );
		$clublist			= array_merge( $clublist, $db->loadObjectList() );
		$lists['clubid']		= JHTML::_('select.genericlist',   $clublist, 'clubid', $ajaxCallback. ' class="inputbox" size="1"','clubid', 'name', $row->clubid );

        // ________build list of teams________
        $sql = 'SELECT t.id as teamid, t.name as teamname'
		. ' FROM #__statzone_teams AS t'
        ;
        if($row->id > 0)// if this is not a new player then no club id would exist
        {
            $sql = $sql . ' WHERE t.clubid = '. $row->clubid;
        }
        $sql = $sql . ' ORDER BY t.name';
        
        $db->setQuery($sql);
		if (!$db->query())
		{
			$this->setRedirect( 'index.php?option=com_statzone' );
			return JError::raiseWarning( 500, $db->getErrorMsg() );
		}
        $teamlist			= $db->loadObjectList() ;
             
        //find the teams that the player belongs too
		 $sql = 'SELECT teamid as teamid'
		. ' FROM #__statzone_playerteams AS pt'
        . ' WHERE pt.playerid = '. $row->id
        ;
        $db->setQuery($sql);
		if (!$db->query())
		{
			$this->setRedirect( 'index.php?option=com_statzone' );
			return JError::raiseWarning( 500, $db->getErrorMsg() );
		}
        $playerteamids =$db->loadObjectList() ;
		$lists['teamid']		= JHTML::_('select.genericlist',   $teamlist, 'teamid[]', 'class="inputbox" size="4" multiple="multiple"','teamid', 'teamname', $playerteamids,'teamid' );
        
        $lists['published'] 		= JHTML::_('select.booleanlist',  'published', 'class="inputbox"', $row->showPlayer );
		        
        require_once(JPATH_COMPONENT.DS.'views'.DS.'players.php');
		StatZoneViewPlayers::singlePlayer( $row, $lists);		
	}

	/**
	 * save a record (and redirect to main page)
	 * @return void
	 */
	function save()
	{
    
        // Check for request forgeries
		JRequest::checkToken() or die( 'Invalid Token' );

		// Initialize variables
		$db =& JFactory::getDBO();
        
		$post	= JRequest::get( 'post' );
		
        $row =& JTable::getInstance('players', 'Table');

		if (!$row->bind( $post )) {
            echo 'Unable to bind: '. $row->getError();
			//return JError::raiseWarning( 500, $row->getError() );
		}
        
        if (!$row->check()) {
            echo 'check failed';
			//return JError::raiseWarning( 500, $row->getError() );
		}
        
        if (!$row->store()) {
            echo 'Store() failed';
			//return JError::raiseWarning( 500, $row->getError() );
		}
        $row->checkin();      
        
        /**/
        $id= $post['id'];
        $name= $post['firstname'] . ' ' . $post['surname'];
        
        $articleId = $post['contentid'];
        if($id > 0)
        {
            $sql = 'UPDATE #__content SET title =\''. $name . '\' WHERE id = '. $articleId ;
            echo 'Heres the SQL: ' . $sql;
            $db->execute($sql);
        }
        else
        {
            //inserting a new content item for this club
            //get the section and category to add this content item 
            $sql = 'SELECT value FROM #__statzone_settings WHERE name = \'SectionID\'';
            $db->setQuery( $sql );
            $sectionid = $db->loadResult(); //section id = 5
            
            $sql = 'SELECT value FROM #__statzone_settings WHERE name = \'PlayerCategoryID\'';
            $db->setQuery( $sql );
            $playercategoryid = $db->loadResult(); //category id clubs = 35 (players = 34, matches = 36)
            
            //add the html along with the plugins to show the player profile
            $content_html='Player name:'. $name. '<br><br> {StatZoneBattingStats}</br></br>{StatZoneBowlingStats}';
            $sql = 'INSERT INTO `#__content` values(\'-1\',\''. $name . '\',\''. $name . '\',\'\',\''.$content_html.'\',\'\', \'1\', \''. $sectionid .'\', \'0\', \''. $playercategoryid .'\', NOW(), \'1\', \'\', \'0000-00-00 00:00:00\', \'1\', \'0\', \'0000-00-00 00:00:00\', CURDATE(), \'0000-00-00 00:00:00\', '
                    .' \'\', \'\', \'pageclass_sfx=\nback_button=\nitem_title=1\nlink_titles=\nintrotext=1\nsection=0\nsection_link=0\ncategory=0\ncategory_link=0\nrating=\nauthor=\ncreatedate=\nmodifydate=\npdf=\nprint=\nemail=\nkeyref=\ndocbook_type=\', \'1\', \'0\', \'1\', \'Player profile, Statzone\', \''.$name.'\', \'0\', \'0\',\'Statzone Players\');';
            
            //$sql = 'INSERT INTO `#__content` values(\'-1\',\''. $name . '\',\''. $name . '\',\'\',\''.$content_html.'\',\'\', \'1\', \''. $sectionid .'\', \'0\', \''. $playercategoryid .'\', NOW(), \'1\', \'\', \'0000-00-00 00:00:00\', \'1\', \'0\', \'0000-00-00 00:00:00\', NOW(), \'0000-00-00 00:00:00\', '
             //       .' \'\', \'\', \'pageclass_sfx=\nback_button=\nitem_title=1\nlink_titles=\nintrotext=1\nsection=0\nsection_link=0\ncategory=0\ncategory_link=0\nrating=\nauthor=\ncreatedate=\nmodifydate=\npdf=\nprint=\nemail=\nkeyref=\ndocbook_type=\', \'1\', \'0\', \'1\', \'Player profile, Statzone\', \''.$name.'\', \'0\', \'0\',\'Statzone Players\');';
                        
                    
            //echo 'Heres the SQL: ' . $sql;
            $db->setQuery($sql);
            if (!$db->query())
            {
                JError::raiseError( 500, $db->stderr() );
                return false;
            }
            $insertedContentId = $db->insertid();
            
            $sql =' UPDATE `#__statzone_players` SET contentid = '. $insertedContentId .' WHERE id = '. $row->id . ';';
            $db->setQuery($sql);
            if (!$db->query())
            {
                JError::raiseError( 500, $db->stderr() );
                return false;
            }
            
        }
        
        //save the players teams        
        $playerteamids = $post['teamid'];
        //$playerteamidsarray[] = split($playerteamids, ',');
        $sql = 'DELETE FROM #__statzone_playerteams WHERE playerid = ' . $row->id;
        $db->setQuery($sql);
        if (!$db->query())
        {
            JError::raiseError( 500, $db->stderr() );
            return false;
        }

        //TODO would like to do this in a big SQL but MySQL is giving me grief. future simple optimization
        //foreach playerteamids, insert a record into the map table:
        for ($i=0, $n=count( $playerteamids); $i < $n; $i++) 
        {
            $sql = ' INSERT INTO #__statzone_playerteams VALUES (0,'.$row->id.','.$playerteamids[$i].')';
            $db->setQuery($sql);
            if (!$db->query())
            {
                JError::raiseError( 500, $db->stderr() );
                return false;
            }
        }
        
        
        //echo $sql;
        
        /**/
        switch ($task)
		{
			case 'apply':
				$link = 'index.php?option=com_statzone&c=player&task=edit&cid[]='. $row->id ;
				break;

			case 'save':
			default:
				$link = 'index.php?option=com_statzone&c=player';
				break;
		}

		$this->setRedirect( $link, JText::_( 'Player ('.$name.') Saved') );
        /**/
        
	}


	/**
	 * remove record(s)
	 * @return void
	 */
	function remove()
	{
        // Check for request forgeries
		JRequest::checkToken() or die( 'Invalid Token' );

		$this->setRedirect( 'index.php?option=com_statzone&c=player' );

		// Initialize variables
		$db		=& JFactory::getDBO();
		$cid	= JRequest::getVar( 'cid', array(), 'post', 'array' );
		$n		= count( $cid );
		JArrayHelper::toInteger( $cid );

		if ($n)
		{
            $query = 'DELETE FROM #__statzone_playerteams'
			. ' WHERE playerid = ' . implode( ' OR id = ', $cid )
			;
			$db->setQuery( $query );
			if (!$db->query()) {
				JError::raiseWarning( 500, $db->stderr());
			}
            
			$query = 'DELETE FROM #__statzone_players'
			. ' WHERE id = ' . implode( ' OR id = ', $cid )
			;
			$db->setQuery( $query );
			if (!$db->query()) {
				JError::raiseWarning( 500, $db->stderr());
			}
		}

		$this->setMessage( JText::sprintf( 'Items removed', $n ) );
	}

    function publish()
	{
		// Check for request forgeries
		JRequest::checkToken() or die( 'Invalid Token' );

		$this->setRedirect( 'index.php?option=com_statzone&c=player' );

		// Initialize variables
		$db			=& JFactory::getDBO();
		$user		=& JFactory::getUser();
		$cid		= JRequest::getVar( 'cid', array(), 'post', 'array' );
		$task		= JRequest::getCmd( 'task' );
		$publish	= ($task == 'publish');
        $n			= count( $cid );

		if (empty( $cid )) {
			return JError::raiseWarning( 500, JText::_( 'No items selected' ) );
		}

		JArrayHelper::toInteger( $cid );
		$cids = implode( ',', $cid );       
        
		$query = 'UPDATE #__statzone_players'
		. ' SET showplayer = ' . (int) $publish
		. ' WHERE id IN ( '. $cids.'  )'
		;
		$db->setQuery( $query );
		if (!$db->query()) {
			return JError::raiseWarning( 500, $row->getError() );
		}
        
        $query = 'UPDATE #__content'
		. ' SET state = ' . (int) $publish
		. ' WHERE id IN ( SELECT contentid FROM #__statzone_players WHERE id IN ('. $cids .'))'
		;
		$db->setQuery( $query );
		if (!$db->query()) {
			return JError::raiseWarning( 500, $row->getError() );
		}
        
		$this->setMessage( JText::sprintf( $publish ? 'Items published' : 'Items unpublished', $n ) );
	}
    
	/**
	 * cancel editing a record
	 * @return void
	 */
	function cancel()
	{
		$msg = JText::_( 'Operation Cancelled' );
		$this->setRedirect( 'index.php?option=com_statzone&c=player', $msg );
	}
    
    /**
	 * Ajax call back function to update the list of teams given the club id selected
	 * @return void
	 */
    function updateTeamlist()
    {
        $db		=& JFactory::getDBO();
		$user	=& JFactory::getUser();
        
        $clubid = JRequest::getCmd('clubid');
                
        // import library
        jimport('joomla.utilities.simplexml');
        // create root node
        $xml = new JSimpleXMLElement('item', array('teamlist' => $teamlist));
        
        
        // ________build list of teams________
        $sql = 'SELECT t.id as teamid, t.name as teamname'
		. ' FROM #__statzone_teams AS t'
        ;
        
        if($clubid >0)
        {
            $sql = $sql . ' WHERE t.clubid = '. $clubid;
        }
        $sql = $sql . ' ORDER BY t.name ';
		$db->setQuery($sql);
		if (!$db->query())
		{
			$this->setRedirect( 'index.php?option=com_statzone' );
            $inputrows =& $xml->addChild('responsedata');
            //return JError::raiseWarning( 500, $db->getErrorMsg() );
            $inputrows->setData('Error in DB or SQL:' . $sql);
            
            $document =& JFactory::getDocument();
            $document->setMimeEncoding('text/xml');
            
            echo '<?xml version="1.0" encoding="UTF-8" ?>'."\n";
            echo $xml->toString();  
            return;
		}

		$teamlist			= $db->loadObjectList() ;
        $lists['teamid']		= JHTML::_('select.genericlist',   $teamlist, 'teamid[]', 'class="inputbox" size="4" multiple="multiple"','teamid', 'teamname', '','teamid' );
        
        //create the child node that will hold data
        $responseData =& $xml->addChild('responsedata');
        
        $responseData->setData($lists['teamid']);//'this is a test prov id = '.$provinceid . ' and teamid = '. $teamid);
        
        $document =& JFactory::getDocument();
        $document->setMimeEncoding('text/xml');
        
        echo '<?xml version="1.0" encoding="UTF-8" ?>'."\n";
        echo $xml->toString();          
               
        
    }
    
    /**
	 * Ajax call back function to update the list of clubs given the province id selected
	 * @return void
	 */
    function updateClublist()
    {
        $db		=& JFactory::getDBO();
		$user	=& JFactory::getUser();
        
        $provinceid = JRequest::getCmd('provinceid');
        //$provinceid = JRequest::getCmd('id');
        
        // import library
        jimport('joomla.utilities.simplexml');
        // create root node
        $xml = new JSimpleXMLElement('item', array('clublist' => $clublist));
        
        
        // ________build list of clubs________
        $sql = 'SELECT c.id as clubid, content.title AS name'
		. ' FROM #__statzone_clubs AS c'
		. ' INNER JOIN #__content AS content ON c.contentid = content.id'
		. ' INNER JOIN #__statzone_province AS provinces ON c.provinceid = provinces.id'
        ;
        
        if($provinceid > 0)
        {
            $sql = $sql . ' WHERE provinces.id = ' . $provinceid ;
        }
        $sql .= ' ORDER BY content.title'; 
		$db->setQuery($sql);
		if (!$db->query())
		{
			$this->setRedirect( 'index.php?option=com_statzone' );
            $inputrows =& $xml->addChild('responseData');
            //return JError::raiseWarning( 500, $db->getErrorMsg() );
            $inputrows->setData($db->getErrorMsg() );
            
            $document =& JFactory::getDocument();
            $document->setMimeEncoding('text/xml');
            
            echo '<?xml version="1.0" encoding="UTF-8" ?>'."\n";
            echo $xml->toString();  
            return;
		}

        $ajaxCallback = 'onchange="document.forms[1].task.value=\'updateTeamlist\';document.forms[1].Submit.click();"';
		$clublist[]		= JHTML::_('select.option',  '0', JText::_( '- Select Club -' ), 'clubid', 'name' );
		$clublist			= array_merge( $clublist, $db->loadObjectList() );
		$lists['clubid']		= JHTML::_('select.genericlist',   $clublist, 'clubid', $ajaxCallback . ' class="inputbox" size="1"','clubid', 'name', $row->clubid );
        
        
        //create the child node that will hold data
        $responseData =& $xml->addChild('responsedata');
        
        $responseData->setData($lists['clubid']);//'this is a test prov id = '.$provinceid . ' and teamid = '. $teamid);
        
        $document =& JFactory::getDocument();
        $document->setMimeEncoding('text/xml');
        //echo 'WASUPPPPPPP' ;
        echo '<?xml version="1.0" encoding="UTF-8" ?>'."\n";
        echo $xml->toString();          
               
        
    }
}
?>
